package com.textmanager.db;

import com.textmanager.model.Directory;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DirectoryDAO {

    public static Directory createDirectory(String name, Integer parentId) throws SQLException {
        String sql = "INSERT INTO directories (name, parent_id) VALUES (?, ?)";
        
        try (Connection conn = DatabaseHelper.connect();
             PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            
            pstmt.setString(1, name);
            if (parentId != null) {
                pstmt.setInt(2, parentId);
            } else {
                pstmt.setNull(2, Types.INTEGER);
            }
            
            int affectedRows = pstmt.executeUpdate();
            if (affectedRows == 0) {
                throw new SQLException("Creating directory failed, no rows affected.");
            }

            try (ResultSet generatedKeys = pstmt.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    return new Directory(generatedKeys.getInt(1), name, parentId);
                } else {
                    throw new SQLException("Creating directory failed, no ID obtained.");
                }
            }
        }
    }

    public static Directory getDirectory(int id) throws SQLException {
        String sql = "SELECT * FROM directories WHERE id = ?";
        
        try (Connection conn = DatabaseHelper.connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
            
            if (rs.next()) {
                return new Directory(
                    rs.getInt("id"),
                    rs.getString("name"),
                    rs.getObject("parent_id") != null ? rs.getInt("parent_id") : null
                );
            }
            return null;
        }
    }

    public static List<Directory> getChildDirectories(Integer parentId) throws SQLException {
        String sql = parentId == null ?
                "SELECT * FROM directories WHERE parent_id IS NULL" :
                "SELECT * FROM directories WHERE parent_id = ?";
        
        try (Connection conn = DatabaseHelper.connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            if (parentId != null) {
                pstmt.setInt(1, parentId);
            }
            
            ResultSet rs = pstmt.executeQuery();
            List<Directory> directories = new ArrayList<>();
            
            while (rs.next()) {
                directories.add(new Directory(
                    rs.getInt("id"),
                    rs.getString("name"),
                    rs.getObject("parent_id") != null ? rs.getInt("parent_id") : null
                ));
            }
            
            return directories;
        }
    }

    public static boolean updateDirectory(Directory directory) throws SQLException {
        String sql = "UPDATE directories SET name = ?, parent_id = ? WHERE id = ?";
        
        try (Connection conn = DatabaseHelper.connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setString(1, directory.getName());
            if (directory.getParentId() != null) {
                pstmt.setInt(2, directory.getParentId());
            } else {
                pstmt.setNull(2, Types.INTEGER);
            }
            pstmt.setInt(3, directory.getId());
            
            return pstmt.executeUpdate() > 0;
        }
    }

    public static boolean deleteDirectory(int id) throws SQLException {
        String sql = "DELETE FROM directories WHERE id = ?";
        
        try (Connection conn = DatabaseHelper.connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, id);
            return pstmt.executeUpdate() > 0;
        }
    }
}